package org.tobuy.dao.impl;
import java.sql.Connection;
import java.sql.Timestamp;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.tobuy.vo.Book;
import org.tobuy.vo.Dvd;
import org.tobuy.vo.Game;
import org.tobuy.vo.Item;
import org.tobuy.vo.User;

import org.tobuy.dao.IItemDAO;

public class ItemDAOImpl implements IItemDAO{
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	ResultSet rs = null;
	public ItemDAOImpl(Connection conn){
		this.conn = conn;
	}

	public List<Item> findItems() throws Exception {
		List<Item> items = new ArrayList<Item>();
		String sql = "SELECT * FROM item";
		this.pstmt = this.conn.prepareStatement(sql);
		rs = this.pstmt.executeQuery();
		while(rs.next()){
			Item i = getItemFromRs(rs);
			items.add(i);
		}
		return items;
	}
	
	public List<Item> findOnSellItems(User user) throws Exception {
		List<Item> items = new ArrayList<Item>();
		String sql = "SELECT * FROM item WHERE sellerid=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1, user.getUser_id());
		ResultSet rs = this.pstmt.executeQuery();
		while(rs.next()){
			Item i = getItemFromRs(rs);
			items.add(i);
		}
		this.pstmt.close();
		return items;
	}

	public List<Item> findItems(String type) throws Exception {
		List<Item> items = new ArrayList<Item>();
		String sql = "SELECT * FROM item WHERE type=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1, type);
		ResultSet rs = this.pstmt.executeQuery();
		while(rs.next()){
			Item i = getItemFromRs(rs);
			items.add(i);
		}
		this.pstmt.close();
		return items;
	}
	public Item findItem(String item_id) throws Exception {
		Item item = new Item();
		String sql="SELECT * FROM item WHERE itemid=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setInt(1, Integer.parseInt(item_id));
		ResultSet rs = this.pstmt.executeQuery();
		rs.next();
		item = getItemFromRs(rs);
		return item;
	}
	public Book findBook(String item_id) throws Exception {
		Book book = new Book();
		
		String sql = "SELECT * FROM item WHERE itemid=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setInt(1, Integer.parseInt(item_id));
		ResultSet rs = this.pstmt.executeQuery();
		rs.next();
		try {
			book.setItem_id(rs.getInt("itemid"));
			book.setType(rs.getString("type"));
			book.setItem_name(rs.getString("name"));
			book.setPrice(rs.getDouble("price"));
			book.setBeschreibung(rs.getString("discr"));
			book.setErstablish_date(rs.getDate("date"));
			book.setAmount(rs.getInt("count"));
			book.setSeller_id(rs.getString("sellerid"));
			book.setIsbn(rs.getString("a1"));
			book.setVerlag(rs.getString("a2"));
			book.setAutor(rs.getString("a3"));
			book.setGebunden(rs.getString("a4"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return book;
		
	}

	public Game findGame(String item_id) throws Exception {
		Game game = new Game();
		String sql = "SELECT * FROM item WHERE itemid=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setInt(1, Integer.parseInt(item_id));
		ResultSet rs = this.pstmt.executeQuery();
		rs.next();
		try {
			game.setItem_id(rs.getInt("itemid"));
			game.setType(rs.getString("type"));
			game.setItem_name(rs.getString("name"));
			game.setPrice(rs.getDouble("price"));
			game.setBeschreibung(rs.getString("discr"));
			game.setErstablish_date(rs.getDate("date"));
			game.setAmount(rs.getInt("count"));
			game.setSeller_id(rs.getString("sellerid"));
			game.setPlatteform(rs.getString("a1"));
			game.setAge_restriction(rs.getString("a2"));
			game.setProducer(rs.getString("a3"));
			game.setCatigory(rs.getString("a4"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return game;
	}

	public Dvd findDvd(String item_id) throws Exception {
		Dvd dvd = new Dvd();
		String sql = "SELECT * FROM item WHERE itemid=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setInt(1, Integer.parseInt(item_id));
		ResultSet rs = this.pstmt.executeQuery();
		rs.next();
		try {
			dvd.setItem_id(rs.getInt("itemid"));
			dvd.setType(rs.getString("type"));
			dvd.setItem_name(rs.getString("name"));
			dvd.setPrice(rs.getDouble("price"));
			dvd.setBeschreibung(rs.getString("discr"));
			dvd.setErstablish_date(rs.getDate("date"));
			dvd.setAmount(rs.getInt("count"));
			dvd.setSeller_id(rs.getString("sellerid"));
			dvd.setPlay_time(rs.getString("a1"));
			dvd.setRegion(rs.getString("a2"));
			dvd.setProducer(rs.getString("a3"));
			dvd.setAge_restriction(rs.getString("a4"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dvd;
	}


	
	public int addItem(Book book, User user) throws Exception {
		int flag = -1;
		String sql = "INSERT INTO item(type,name,price,discr,date,count,sellerid,a1,a2,a3,a4) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
		this.pstmt = this.conn.prepareStatement(sql,1);
		this.pstmt.setString(1, book.getType());
		this.pstmt.setString(2, book.getItem_name());
		this.pstmt.setDouble(3, book.getPrice());
		this.pstmt.setString(4, book.getBeschreibung());
		this.pstmt.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
		this.pstmt.setInt(6, book.getAmount());
		this.pstmt.setString(7, user.getUser_id());
		this.pstmt.setString(8, book.getIsbn());
		this.pstmt.setString(9, book.getVerlag());
		this.pstmt.setString(10, book.getAutor());
		this.pstmt.setString(11, book.getGebunden());
		
		pstmt.executeUpdate();
		rs=pstmt.getGeneratedKeys();
		rs.next();
		flag=rs.getInt(1);
		this.pstmt.close();
		return flag;
	}

	public int addItem(Game game, User user) throws Exception {
		int flag = -1;
		String sql = "INSERT INTO item(type,name,price,discr,date,count,sellerid,a1,a2,a3,a4) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
		this.pstmt = this.conn.prepareStatement(sql,1);
		this.pstmt.setString(1, game.getType());
		this.pstmt.setString(2, game.getItem_name());
		this.pstmt.setDouble(3, game.getPrice());
		this.pstmt.setString(4, game.getBeschreibung());
		this.pstmt.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
		this.pstmt.setInt(6, game.getAmount());
		this.pstmt.setString(7, user.getUser_id());
		this.pstmt.setString(8, game.getPlatteform());
		this.pstmt.setString(9, game.getAge_restriction());
		this.pstmt.setString(10, game.getProducer());
		this.pstmt.setString(11, game.getCatigory());
		
		pstmt.executeUpdate();
		rs=pstmt.getGeneratedKeys();
		rs.next();
		flag=rs.getInt(1);
		this.pstmt.close();
		return flag;
	}

	public int addItem(Dvd dvd, User user) throws Exception {
		int flag = -1;
		String sql = "INSERT INTO item(type,name,price,discr,date,count,sellerid,a1,a2,a3,a4) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
		this.pstmt = this.conn.prepareStatement(sql,1);
		this.pstmt.setString(1, dvd.getType());
		this.pstmt.setString(2, dvd.getItem_name());
		this.pstmt.setDouble(3, dvd.getPrice());
		this.pstmt.setString(4, dvd.getBeschreibung());
		this.pstmt.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
		this.pstmt.setInt(6, dvd.getAmount());
		this.pstmt.setString(7, user.getUser_id());
		this.pstmt.setString(8, dvd.getPlay_time());
		this.pstmt.setString(9, dvd.getRegion());
		this.pstmt.setString(10, dvd.getProducer());
		this.pstmt.setString(11, dvd.getAge_restriction());
		
		pstmt.executeUpdate();
		rs=pstmt.getGeneratedKeys();
		rs.next();
		flag=rs.getInt(1);
		this.pstmt.close();
		return flag;
	}

	public boolean deleteItem(String item_id) throws Exception {
		boolean flag = false;
		String sql = "DELETE FROM item where itemid=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1, item_id);
		if(pstmt.executeUpdate()!=0){
			flag = true;
		}
		this.pstmt.close();
		return flag;
	}

	public boolean changeAmount(int amount, String item_id) throws Exception {
		boolean flag = false;
		String sql = "UPDATE item SET count=? where itemid=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setInt(1, amount);
		this.pstmt.setString(2, item_id);
		if(pstmt.executeUpdate()!=0){
			flag = true;
		}
		this.pstmt.close();
		return flag;
	}

	private Item getItemFromRs(ResultSet rs){
		Item item = new Item();
			try {
				item.setItem_id(rs.getInt("itemid"));
				item.setType(rs.getString("type"));
				item.setItem_name(rs.getString("name"));
				item.setPrice(rs.getDouble("price"));
				item.setSeller_id(rs.getString("sellerid"));
				item.setBeschreibung(rs.getString("discr"));
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		return item;
	}

	public int last_insert_id() throws Exception {
		
		String sql = "SELECT LAST_INSERT_ID()";
		this.pstmt = this.conn.prepareStatement(sql);
		rs = this.pstmt.executeQuery();
		rs.next();
		int last_insert_id = rs.getInt(1);
		return last_insert_id;
	}






	

	

}
